/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package bdd;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author amine
 */
public final class Service {

    Connection connection;
    private String user = "";
    private String password = "";

    public Service() {
    }

    /**
     * Init the BDD connection.
     * @param user  String - DB user name.
     * @param password  String - DB corresponding password.
     */
    public Service(String user, String password) {
        this.user = user;
        this.password = password;
        connect();
    }

    /**
     * Connection to MySql DB using the driver in WEB-INF/lib/
     * Mysql server : localhost
     * Mysql used base : esgi_pki
     * @return boolean - True if the connection is established, false otherwise.
     */
    public boolean connect() {
        try {
            System.out.println("Loading driver...");
            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("Driver loaded!");
            connection = DriverManager.getConnection("jdbc:mysql://localhost/esgi_pki", user, password);
            return true;
        } catch (Exception ex) {
            Logger.getLogger(Service.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

    /**
     * Insert a new user.
     * @param login
     * @param password
     * @param first_name
     * @param last_name
     * @param company
     * @param country
     * @param state
     * @return
     * @throws SQLException 
     */
    public int insertRegistration(String login, String password, String first_name, String last_name, String company, String country, String state) throws SQLException {
        PreparedStatement ps = connection.prepareStatement("INSERT INTO `esgi_pki`.`users` (`login`, `password`, `first_name`, `last_name`, `company`, `country`, `state`, `creation_date`) "
                + "VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
        ps.setString(1, login);
        ps.setString(2, password);
        ps.setString(3, first_name);
        ps.setString(4, last_name);
        ps.setString(5, company);
        ps.setString(6, country);
        ps.setString(7, state);
        ps.setDate(8, new java.sql.Date((new java.util.Date()).getTime()));
        ps.executeUpdate();
        return logIn(login, password);
    }

    /**
     * Check if the given login exists in the table "users".
     * @param login String - login to look for in the "users" tables.
     * @return  True if login exists; false if the result set contains no rows. 
     * @throws SQLException 
     */
    public boolean loginIsExist(String login) throws SQLException {
        PreparedStatement ps = connection.prepareStatement("select * from users where login='" + login + "'");
        ResultSet rs = ps.executeQuery();
        return rs.isBeforeFirst();
    }

    /**
     * Check if the login and password are correct.
     * @param login
     * @param password
     * @return  int - the user id or -1 if the couple login/password is incorrect.
     * @throws SQLException 
     */
    public int logIn(String login, String password) throws SQLException {
        PreparedStatement ps = connection.prepareStatement("select * from users where login='" + login + "'" + "and password='" + password + "'");
        ResultSet rs = ps.executeQuery();
        if (!rs.isBeforeFirst()) {
            return -1;
        } else {
            if (rs.next()) {
                return rs.getInt("id_user");
            } else {
                return -1;
            }
        }
    }

    /**
     * Insert a new certificate in tables "certificates".
     * @param user      int - user id
     * @param private_key   String - a private key, created with RSA and encoded with sun.misc.BASE64Encoder.
     * @param public_key    String - a public key, created with RSA and encoded with sun.misc.BASE64Encoder.
     * @param expDate       java.sql.Date - The expiration date.
     * @param active        boolean - Tell if the keys are active (access to public).
     * @return              boolean - True if the insertion was successfull, false otherwise.
     * @throws SQLException 
     */
    public boolean insertCertificates(int user, String private_key, String public_key, java.sql.Date expDate, boolean active) throws SQLException {
        PreparedStatement ps = connection.prepareStatement("INSERT INTO certificates (`id_user`, `public_key`, `private_key`, `creation_date`, `date_expiration`, `active`) "
                + "VALUES (?, ?, ?, ?, ?, ?)");
        ps.setInt(1, user);
        ps.setString(2, public_key);
        ps.setString(3, private_key);
        ps.setDate(4, new java.sql.Date((new java.util.Date()).getTime()));
        ps.setDate(5, expDate);
        ps.setBoolean(6, active);
        return ps.execute();
    }

    /**
     * Return the list of certificates that belongs to a user.
     * @param user  int - user id.
     * @return      List<List<String>> - List containing the list of properties for each certificates.
     * @throws SQLException 
     */
    public List<List<String>> getUserCertificates(int user) throws SQLException {

        List<List<String>> list = new ArrayList<List<String>>();
        PreparedStatement ps = connection.prepareStatement("select * from certificates where id_user=" + user);
        ResultSet rs = ps.executeQuery();

        while (rs.next()) {
            List<String> certificates = new ArrayList<String>();
            certificates.add(rs.getString("public_key"));
            certificates.add(rs.getString("private_key"));
            certificates.add(rs.getString("creation_date"));
            certificates.add(rs.getString("date_expiration"));
            certificates.add(rs.getString("active"));
            certificates.add(rs.getString("id"));
            list.add(certificates);
        }
        return list;
    }
    
    /**
     * Return the private key of a certificates.
     * @param idCertificate int - Certificate id.
     * @return  String - a private key, created with RSA and encoded with sun.misc.BASE64Encoder. 
     * @throws SQLException 
     */
    public String getPrivateKey(int idCertificate) throws SQLException {

        PreparedStatement ps = connection.prepareStatement("select * from certificates where id=" + idCertificate);
        ResultSet rs = ps.executeQuery();

        while (rs.next()) {
            return rs.getString("private_key");
        }
        return null;
    }

    /**
     * Get all active certificates.
     * @return  List<List<String>> - List containing the list of properties for each certificates.
     * @throws SQLException 
     */
    public List<List<String>> getCertificates() throws SQLException {
        List<List<String>> listRes = new ArrayList<List<String>>();
        PreparedStatement ps = connection.prepareStatement("SELECT users.login, cer.public_key, cer.creation_date, cer.date_expiration FROM certificates cer,users WHERE users.id_user= cer.id_user and cer.active=1");
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            List<String>list = new ArrayList<String>();
            list.add(rs.getString("login"));
            list.add(rs.getString("public_key"));
            list.add(rs.getString("creation_date"));
            list.add(rs.getString("date_expiration"));
            listRes.add(list);
        }
        return listRes;
    }

    /**
     * Revoke a certificates.
     * @param id    int - Certificates id
     * @return  True if the revokation was successfull, false otherwise.
     * @throws SQLException 
     */
    public boolean revokeKey(int id) throws SQLException {
        PreparedStatement ps = connection.prepareStatement("SELECT id_user,public_key FROM certificates WHERE id=" + id);
        ResultSet rs = ps.executeQuery();
        Integer id_user = null;
        String key = null;
        while (rs.next()) {
            key = rs.getString("public_key");
            id_user = rs.getInt("id_user");
        }
        ps = connection.prepareStatement("DELETE FROM certificates WHERE id=" + id);
        ps.execute();
        ps = connection.prepareStatement("INSERT INTO revocate_certificates (`id_user`, `public_key`,`date_revocation`) "
                + "VALUES (?, ?, ?)");
        ps.setInt(1, id_user);
        ps.setString(2, key);
        ps.setDate(3, new java.sql.Date((new java.util.Date()).getTime()));
        return ps.execute();
    }

    /**
     * Get the list of revoked certificates.
     * @return  List<List<String>> - List containing the list of properties for each certificates.
     * @throws SQLException 
     */
    public List<List<String>> getRevokeCertificates() throws SQLException {
        List<List<String>> list = new ArrayList<List<String>>();
        PreparedStatement ps = connection.prepareStatement("SELECT * FROM revocate_certificates rev, users user where rev.id_user=user.id_user");
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            List<String> certificates = new ArrayList<String>();
//            certificates.add(rs.getString("rev.id"));
//            certificates.add(rs.getString("rev.id_user"));
            certificates.add(rs.getString("user.login"));
            certificates.add(rs.getString("rev.public_key"));
            certificates.add(rs.getString("rev.date_revocation"));
            list.add(certificates);
        }
        return list;
    }

    /**
     * Activate a certificates.
     * @param id    int - Certificate id.
     * @return      True if the activation was successfull, false otherwise.
     * @throws SQLException 
     */
    public boolean activateCertificate(int id) throws SQLException {
        PreparedStatement ps = connection.prepareStatement("UPDATE certificates SET active=1 WHERE id=" + id);
        return ps.execute();
    }

    /**
     * Delete a certificate.
     * @param id    int - Certificate id.
     * @return      True if the certificates was successfully deleted, false otherwise.
     * @throws SQLException 
     */
    public boolean removeKey(int id) throws SQLException {
        PreparedStatement ps = connection.prepareStatement("DELETE FROM certificates WHERE id=" + id);
        return ps.execute();
    }
}
